﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Runtime.Remoting.Contexts;
using Oracle.ManagedDataAccess.Client;

namespace WindowsFormsTest
{
    /// <summary>
    ///     扩展方法(静态方法必须在静态类中)
    /// </summary>
    public static class Extensions
    {
        /// <summary>
        ///     EF SQL 语句返回 dataTable
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable SqlQueryForDataTable(this Database db, string sql)
        {
            OracleConnection con = new OracleConnection();
            con = (OracleConnection)db.Connection;
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = sql;
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();
            return dt;
        }

        /// <summary>
        ///     EF SQL 语句返回 dataTable
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable SqlQueryForDataTableByEF(this Database db, string sql)
        {
            if(db.Connection.State==ConnectionState.Closed) db.Connection.Open();
            System.Data.Common.DbCommand cmd = db.Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = db.Connection;
            //转成sqlcommand正常查询语句。
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = cmd as OracleCommand;
            DataTable dt = new DataTable();
            da.Fill(dt);
            db.Connection.Close();
            return dt;
        }

        public static DataTable ExcuteDataTable(this Database db, string sql, params object[] parameters)
        {
            if (db.Connection.State == ConnectionState.Closed) db.Connection.Open();
            DataTable dt = new DataTable();
            System.Data.Common.DbCommand cmd = db.Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = db.Connection;
            //2.创建适配器
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = cmd as OracleCommand;
            List<OracleParameter> oracleParametersList = new List<OracleParameter>();
            //2.1设置查询命令的参数
            //for (int i = 0;i<parameters.Length; i++)
            //{
            //    OracleParameter oracleParameter = new OracleParameter();
            //    oracleParameter.ParameterName = "p" + i;
            //    oracleParameter.Value = parameters[i].ToString();
            //    oracleParametersList.Add(oracleParameter);
            //} 
            //或者for可以替换foreach成关键字
            int i2 = 0;
            foreach (var parm in parameters)
            {
                OracleParameter oracleParameter = new OracleParameter();
                oracleParameter.ParameterName = "p" + i2;
                oracleParameter.Value = parm;
                oracleParametersList.Add(oracleParameter);
            }

            da.SelectCommand.Parameters.AddRange(oracleParametersList.ToArray());
            //4.将数据查询并填充到数据表中
            da.Fill(dt);
            db.Connection.Close();
            //打印对应的SQL语句
            PrintSQL(cmd);
            return dt; 
        }

        public static DataTable ExcuteDataTableByParam(this Database db, string sql, params OracleParameter[] parameters)
        {
            if (db.Connection.State == ConnectionState.Closed) db.Connection.Open();
            DataTable dt = new DataTable();
            System.Data.Common.DbCommand cmd = db.Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = db.Connection;
            //2.创建适配器
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = cmd as OracleCommand;
            //2.1设置查询命令的参数
            da.SelectCommand.Parameters.AddRange(parameters);
            //4.将数据查询并填充到数据表中
            da.Fill(dt);
            db.Connection.Close();
            //打印对应的SQL语句
            PrintSQL(cmd);
            return dt;
        }

        public static void PrintSQL(DbCommand command)
        {
            if (command.Connection is OracleConnection)
            {
                command.CommandText = command.CommandText.Replace("@", ":");
            }
            string sql = command.CommandText;

            foreach (var parm in command.Parameters)
            {
                //parm是参数名字段
                string outPutParamValue = command.Parameters[$"{parm}"].Value.ToString();//参数值
                if (command.Connection is SqlConnection)
                {
                    sql = sql.Replace($"@{parm}", $"'{outPutParamValue}'");
                }
                else if (command.Connection is OracleConnection)
                {
                    sql = sql.Replace($":{parm}", $"'{outPutParamValue}'");
                }

                //获取对应的参数的值：
                //Console.WriteLine($"参数名=参数值是：{parm}={outPutParamValue}");//输出看看。
            }

            //Console.WriteLine($"重写LogCommand:记录将要执行的命令:{command.CommandText}");
            Console.WriteLine($"输出的SQL语句：\r\n{sql}{Environment.NewLine}");
        }
    }
}